package com.pk.platform.business.report.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.pk.platform.business.charge.dao.IChargeIndexDao;
import com.pk.platform.business.core.dao.impl.GenericDaoImpl;
import com.pk.platform.business.report.dao.IRefundReportDao;
import com.pk.platform.business.report.vo.RefundReportVO;
import com.pk.platform.domain.charge.ChargeIndex;
import com.pk.platform.util.StringConverter;

public class RefundReportDaoImpl extends GenericDaoImpl implements
		IRefundReportDao {

	private IChargeIndexDao chargeIndexDao;
	
	public List<Map<String, Object>> queryRefundReportList(RefundReportVO rrvo) {
		List<Object> param = new ArrayList<Object>();
		String sql = this.getRefundReportSql(rrvo, param);
		return this.getSjt().queryForList(sql, param.toArray());
	}
	
	private String getRefundReportSql(RefundReportVO rrvo, List<Object> param)
	{
		StringBuffer head = new StringBuffer();
		StringBuffer sqlIndex = new StringBuffer();
		List<ChargeIndex> indexList = chargeIndexDao.queryAllChargeIndexList();
		int len = indexList.size();
		for(int i = 0; i < len; i++)
		{
			ChargeIndex index = indexList.get(i);
			head.append(index.getIndexName());
			sqlIndex.append(" sum(case when ciId = '").append(index.getId()).append("' then amount end) as '").append(index.getIndexName()).append("'");
			if(i != len - 1)
			{
				head.append(",");
				sqlIndex.append(",");
			}
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.childNum, t1.childName, t1.year, January, February, March,");
		sql.append("  April, May, June, July, August, September, October, November, December");
		if(StringConverter.isNotNull(head.toString()))
		{
			sql.append(",").append(head.toString());
		}
		sql.append(" from (select max(c.childName) childName,");
		sql.append(" c.id childId,");
		sql.append(" max(c.childNum) childNum,");
		sql.append(" rd.year");
		
		if(StringConverter.isNotNull(sqlIndex.toString()))
		{
			sql.append(",").append(sqlIndex.toString());
		}
		
		sql.append(" from refund r left join child c on r.childId = c.id,");
		sql.append(" (select t.*, date_format(t.chargeDate, '%Y') year, date_format(t.chargeDate, '%m') month from refund_detail t) rd");
		sql.append(" where r.id = rd.rfId");
		sql.append(" and r.kgId = '").append(rrvo.getKindergartenId()).append("'");
		sql.append(" group by c.id, rd.year");
		sql.append(" order by c.childNum, rd.chargeDate");
		sql.append(" ) t1, ");
		sql.append(" (select c.id childId,");
		sql.append(" rd.year,");
		
		sql.append(" sum(case when month = '01' then amount end) as January,");
		sql.append(" sum(case when month = '02' then amount end) as February,");
		sql.append(" sum(case when month = '03' then amount end) as March,");
		sql.append(" sum(case when month = '04' then amount end) as April,");
		sql.append(" sum(case when month = '05' then amount end) as May,");
		sql.append(" sum(case when month = '06' then amount end) as June,");
		sql.append(" sum(case when month = '07' then amount end) as July,");
		sql.append(" sum(case when month = '08' then amount end) as August,");
		sql.append(" sum(case when month = '09' then amount end) as September,");
		sql.append(" sum(case when month = '00' then amount end) as October,");
		sql.append(" sum(case when month = '11' then amount end) as November,");
		sql.append(" sum(case when month = '12' then amount end) as December");
		
		sql.append(" from refund r left join child c on r.childId = c.id,");
		sql.append(" (select t.*, date_format(t.chargeDate, '%Y') year, date_format(t.chargeDate, '%m') month from refund_detail t) rd");
		sql.append(" where r.id = rd.rfId");
		sql.append(" and r.kgId = '").append(rrvo.getKindergartenId()).append("'");
		sql.append(" group by c.id, rd.year");
		sql.append(" order by c.childNum, rd.chargeDate");
		sql.append(" ) t2 where t1.childId = t2.childId and t1.year = t2.year");
		
		if(StringConverter.isNotNull(rrvo.getChildNum()))
		{
			sql.append(" and t1.childNum like ?");
			param.add("%" + rrvo.getChildNum() + "%");
		}
		if(StringConverter.isNotNull(rrvo.getChildName()))
		{
			sql.append(" and t1.childName like ?");
			param.add("%" + rrvo.getChildName() + "%");
		}
		if(StringConverter.isNotNull(rrvo.getPeriodStart()))
		{
			sql.append(" and t1.year >= ?");
			param.add(rrvo.getPeriodStart());
		}
		if(StringConverter.isNotNull(rrvo.getPeriodEnd()))
		{
			sql.append(" and t1.year <= ?");
			param.add(rrvo.getPeriodEnd());
		}
		return sql.toString();
	}
	
	public IChargeIndexDao getChargeIndexDao() {
		return chargeIndexDao;
	}

	public void setChargeIndexDao(IChargeIndexDao chargeIndexDao) {
		this.chargeIndexDao = chargeIndexDao;
	}

}
